﻿using NPOI.HSSF.UserModel;
using NPOI.SS.Formula.Eval;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;

namespace AngelCMS.module.Models
{

    public enum CellDataType
    {
        Date,
        Time,
        DateTime,
        String
    }


    public class NPOIHelper
    {
        /// <summary>    
        /// 由Excel导入DataTable    
        /// </summary>    
        /// <param name="excelStream">Excel文件流</param>   
        /// <param name="sheetName">Excel工作表</param>    
        /// <param name="sheetIndex">Excel工作表</param>    
        /// <param name="headerRowIndex">Excel表头行索引</param>    
        /// <returns>DataTable</returns>    
        public static DataTable ExcelToDataTable(Stream excelStream, string sheetName, int sheetIndex, int headerRowIndex)
        {

            IWorkbook workbook = null;
            workbook = WorkbookFactory.Create(excelStream);
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
            }
            else
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dataRow[j] = GetText(cell);
                }
                table.Rows.Add(dataRow);
            }

            #region 处理合并单元格

            int ij = sheet.NumMergedRegions;

            for (int i = 0; i < ij; i++)
            {
                CellRangeAddress r = sheet.GetMergedRegion(i);
                if (r.FirstRow <= headerRowIndex)
                    continue;
                object o = table.Rows[r.FirstRow - (headerRowIndex + 1)][r.FirstColumn];
                for (int jr = r.FirstRow; jr <= r.LastRow; jr++)
                {
                    if ((jr - (headerRowIndex + 1)) < table.Rows.Count)//处理合并列大于数据总列时这种不规范的EXCEL文本，
                    {
                        for (int jc = r.FirstColumn; jc <= r.LastColumn; jc++)
                        {
                            table.Rows[jr - (headerRowIndex + 1)][jc] = o;
                        }
                    }
                }

            }

            #endregion
            excelStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

        public static DataTable ExcelToDataTable(IWorkbook workbook, string sheetName, int sheetIndex, int headerRowIndex)
        {

          
            ISheet sheet = null;
            if (!string.IsNullOrEmpty(sheetName))
            {
                sheet = workbook.GetSheet(sheetName);
            }
            else
            {
                sheet = workbook.GetSheetAt(sheetIndex);
            }
            DataTable table = new DataTable();
            IRow headerRow = sheet.GetRow(headerRowIndex);
            int cellCount = headerRow.LastCellNum;
            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
                table.Columns.Add(column);
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                IRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    ICell cell = row.GetCell(j);
                    dataRow[j] = GetText(cell);
                }
                table.Rows.Add(dataRow);
            }

            #region 处理合并单元格

            int ij = sheet.NumMergedRegions;

            for (int i = 0; i < ij; i++)
            {
                CellRangeAddress r = sheet.GetMergedRegion(i);
                if (r.FirstRow <= headerRowIndex)
                    continue;
                object o = table.Rows[r.FirstRow - (headerRowIndex + 1)][r.FirstColumn];
                for (int jr = r.FirstRow; jr <= r.LastRow; jr++)
                {
                    if ((jr - (headerRowIndex + 1)) < table.Rows.Count)//处理合并列大于数据总列时这种不规范的EXCEL文本，
                    {
                        for (int jc = r.FirstColumn; jc <= r.LastColumn; jc++)
                        {
                            table.Rows[jr - (headerRowIndex + 1)][jc] = o;
                        }
                    }
                }

            }

            #endregion
        
            workbook = null;
            sheet = null;
            return table;
        }


        /// <summary>    
        /// 由Excel导入DataTable   
        /// </summary>   
        ///  <param name="excelFilePath">Excel文件路径，为物理路径。</param>   
        /// <param name="sheetName">Excel工作表名称</param>    
        /// <param name="headerRowIndex">Excel表头行索引</param>    
        /// <returns>DataTable</returns>    
        public static DataTable ExcelToDataTable(string excelFilePath, string sheetName, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ExcelToDataTable(stream, sheetName, -1, headerRowIndex);
            }
        }

        /// <summary>    
        /// 由Excel导入DataTable    
        /// </summary>    
        /// <param name="excelFilePath">Excel文件路径，为物理路径。</param>    
        /// <param name="sheetName">Excel工作表索引</param>    
        /// <param name="headerRowIndex">Excel表头行索引</param>    
        /// <returns>DataTable</returns>    
        public static DataTable ExcelToDataTable(string excelFilePath, int sheetIndex, int headerRowIndex)
        {
            using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
            {
                return ExcelToDataTable(stream, null, sheetIndex, headerRowIndex);
            }


        }

        private static CellDataType GetCellDataType(ICell cell)
        {
            string dataFormatString = cell.CellStyle.GetDataFormatString();

            if (dataFormatString.ToLower().IndexOf("y") != -1 && dataFormatString.ToLower().IndexOf("h") != -1)
            {
                return CellDataType.DateTime;
            }
            else if (dataFormatString.ToLower().IndexOf("y") != -1 && dataFormatString.ToLower().IndexOf("h") == -1)
            {
                return CellDataType.Date;
            }
            else if (dataFormatString.ToLower().IndexOf("y") == -1 && dataFormatString.ToLower().IndexOf("h") != -1)
            {
                return CellDataType.Time;
            }
            else
            {
                return CellDataType.String;
            }
        }

        private static string GetText(ICell cell)
        {
            if (cell == null)
            {
                return "";
            }

            string value = null;
            switch (cell.CellType)
            {
                case CellType.Error:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Formula:
                    value = GetFormulaText(cell);
                    break;
                case CellType.Numeric:
                    CellDataType cdt = GetCellDataType(cell);
                    if (cdt == CellDataType.DateTime)
                    {
                        value = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss");
                    }
                    else if (cdt == CellDataType.Date)
                    {
                        value = cell.DateCellValue.ToString("yyyy-MM-dd");
                    }
                    else if (cdt == CellDataType.Time)
                    {
                        value = cell.DateCellValue.ToString("HH:mm:ss");
                    }
                    else
                    {
                        value = cell.NumericCellValue.ToString();
                    }
                    break;
                case CellType.String:
                    value = cell.StringCellValue;
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }

        private static string GetFormulaText(ICell cell)
        {
            string value = null;
            switch (cell.CachedFormulaResultType)
            {
                case CellType.Error:
                    value = ErrorEval.GetText(cell.ErrorCellValue);
                    break;
                case CellType.Numeric:
                    value = cell.NumericCellValue.ToString();
                    break;
                case CellType.String:
                    value = cell.StringCellValue;
                    break;
                default:
                    value = cell.ToString();
                    break;
            }
            return value;
        }

        /// <summary>
        ///
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="strExcelFileName"></param>
        /// <returns></returns>
        public static bool ToExportExcelHeJi(DataTable dt, string strExcelFileName)
        {
            //Datatable导出Excel
            #region 导出Excel

            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {

                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);

                /******************写入标题行，合并居中*********************/
                IRow row = sheet.CreateRow(0);
                ICell cellhead = row.CreateCell(0);
                cellhead.SetCellValue(strExcelFileName);
                ICellStyle style = workbook.CreateCellStyle();

                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                style.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeight = 20 * 20;
                font.Boldweight = (short)FontBoldWeight.Normal;
                style.SetFont(font);
                cellhead.CellStyle = style;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                /******************写入列标题*********************/

                //用column name 作为列名
                int icolIndex = 0;//行号，第二行
                IRow headerRow = sheet.CreateRow(1);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 2;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {

                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }


                #region 以文件形式写入到服务器硬盘上方法
                string folder = HttpContext.Current.Server.MapPath("~/Excel");
                if (!Directory.Exists(folder))
                    Directory.CreateDirectory(folder);
                FileStream file = new FileStream(folder + "/" + strExcelFileName + ".xls", FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
                #endregion
                //#region 写入内存(写到客户端)


                ////设置响应的类型为Excel
                //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                ////设置下载的Excel文件名
                //HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", strExcelFileName + ".xls"));
                ////Clear方法删除所有缓存中的HTML输出。但此方法只删除Response显示输入信息，不删除Response头信息。以免影响导出数据的完整性。
                //HttpContext.Current.Response.Clear();

                //using (MemoryStream ms = new MemoryStream())
                //{
                //    //将工作簿的内容放到内存流中
                //    workbook.Write(ms);
                //    //将内存流转换成字节数组发送到客户端
                //    HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                //    HttpContext.Current.Response.End();
                //}

                //#endregion
                return true;
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            finally { workbook = null; }
            // return false;

            #endregion
        }

        /// <summary>
        /// 扩展DataTable导出到Excel文件
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="strExcelFileName">导出的Excel文件名</param>
        public static bool ToExportExcel(DataTable dt, string strExcelFileName)
        {
            //Datatable导出Excel
            #region 导出Excel

            HSSFWorkbook workbook = new HSSFWorkbook();
            try
            {

                ISheet sheet = workbook.CreateSheet("Sheet1");

                ICellStyle HeadercellStyle = workbook.CreateCellStyle();
                HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                //字体
                NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
                headerfont.Boldweight = (short)FontBoldWeight.Bold;
                HeadercellStyle.SetFont(headerfont);

                /******************写入标题行，合并居中*********************/
                IRow row = sheet.CreateRow(0);
                ICell cellhead = row.CreateCell(0);
                cellhead.SetCellValue(strExcelFileName);
                ICellStyle style = workbook.CreateCellStyle();

                style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

                style.Alignment = HorizontalAlignment.Center;
                IFont font = workbook.CreateFont();
                font.FontHeight = 20 * 20;
                font.Boldweight = (short)FontBoldWeight.Normal;
                style.SetFont(font);
                cellhead.CellStyle = style;
                sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
                /******************写入列标题*********************/

                //用column name 作为列名
                int icolIndex = 0;//行号，第二行
                IRow headerRow = sheet.CreateRow(1);
                foreach (DataColumn item in dt.Columns)
                {
                    ICell cell = headerRow.CreateCell(icolIndex);
                    cell.SetCellValue(item.ColumnName);
                    cell.CellStyle = HeadercellStyle;
                    icolIndex++;
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();

                //为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
                cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
                cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;


                NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
                cellfont.Boldweight = (short)FontBoldWeight.Normal;
                cellStyle.SetFont(cellfont);

                //建立内容行
                int iRowIndex = 2;
                int iCellIndex = 0;
                foreach (DataRow Rowitem in dt.Rows)
                {
                    IRow DataRow = sheet.CreateRow(iRowIndex);
                    foreach (DataColumn Colitem in dt.Columns)
                    {

                        ICell cell = DataRow.CreateCell(iCellIndex);
                        cell.SetCellValue(Rowitem[Colitem].ToString());
                        cell.CellStyle = cellStyle;
                        iCellIndex++;
                    }
                    iCellIndex = 0;
                    iRowIndex++;
                }

                //自适应列宽度
                for (int i = 0; i < icolIndex; i++)
                {
                    sheet.AutoSizeColumn(i);
                }


                #region 以文件形式写入到服务器硬盘上方法
                string folder = HttpContext.Current.Server.MapPath("~/Excel");
                if (!Directory.Exists(folder))
                    Directory.CreateDirectory(folder);
                FileStream file = new FileStream(folder + "/" + strExcelFileName + ".xls", FileMode.OpenOrCreate);
                workbook.Write(file);
                file.Flush();
                file.Close();
                #endregion
                //#region 写入内存(写到客户端)


                ////设置响应的类型为Excel
                //HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
                ////设置下载的Excel文件名
                //HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}", strExcelFileName + ".xls"));
                ////Clear方法删除所有缓存中的HTML输出。但此方法只删除Response显示输入信息，不删除Response头信息。以免影响导出数据的完整性。
                //HttpContext.Current.Response.Clear();

                //using (MemoryStream ms = new MemoryStream())
                //{
                //    //将工作簿的内容放到内存流中
                //    workbook.Write(ms);
                //    //将内存流转换成字节数组发送到客户端
                //    HttpContext.Current.Response.BinaryWrite(ms.GetBuffer());
                //    HttpContext.Current.Response.End();
                //}

                //#endregion
                return true;
            }
            catch (Exception ex) { throw new Exception(ex.ToString()); }
            finally { workbook = null; }
            // return false;

            #endregion
        }

    }
}